package com.asiainfo.crm.core.rule;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import com.asiainfo.crm.core.base.SqlmapBaseDao;
import com.asiainfo.crm.core.common.BeanFactory;
import com.asiainfo.crm.core.exception.ErrorCode;
import com.asiainfo.crm.core.exception.SystemException;

public class DBUtils extends SqlmapBaseDao {
	public static Connection getConnection() {
		Connection conn = null;
		try {

			DataSource ds = (DataSource) BeanFactory.getBean("dataSource");
			conn = ds.getConnection();

			// Class.forName("oracle.jdbc.driver.OracleDriver");
			// conn = DriverManager.getConnection(
			// "jdbc:oracle:thin:@localhost:1521:home", "frame", "frame");
		} catch (Exception e) {
			throw new SystemException(ErrorCode.DB_ERROR, e);
		}
		return conn;
	}

	/*
	 * public static List execSql(String sql, List conditions) { List result =
	 * null; Connection conn = null; PreparedStatement st = null; ResultSet rs =
	 * null; try { conn = getConnection(); st = conn.prepareStatement(sql); if
	 * (conditions != null) { for (int i = 0; i < conditions.size(); i++) {
	 * setParamValue(st, i + 1, conditions.get(i)); } } rs = st.executeQuery();
	 * 
	 * while (rs.next()) { List lstColumn = new ArrayList(); ResultSetMetaData
	 * rsmd = rs.getMetaData(); int cols = rsmd.getColumnCount();
	 * 
	 * for (int i = 0; i < cols; i++) { lstColumn.add(rs.getString(i + 1)); }
	 * result.add(lstColumn); } } catch (Exception e) { throw new
	 * SystemException(ErrorCode.DB_ERROR, e); } finally { try { rs.close(); }
	 * catch (Exception e) { } try { st.close(); } catch (Exception e) { } try {
	 * conn.close(); } catch (Exception e) { } } return result; }
	 */
	public static List getValue(String sql, List conditions) {
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		List result = new ArrayList();
		try {
			conn = getConnection();
			st = conn.prepareStatement(sql);
			if (conditions != null) {
				for (int i = 0; i < conditions.size(); i++) {
					setParamValue(st, i + 1, conditions.get(i));
				}
			}
			rs = st.executeQuery();
			if (rs.next()) {
				ResultSetMetaData rsmd = rs.getMetaData();
				int cols = rsmd.getColumnCount();

				for (int i = 0; i < cols; i++) {
					result.add(rs.getString(i + 1));
				}
			}
		} catch (Exception e) {
			throw new SystemException(ErrorCode.DB_ERROR, e);
		} finally {
			try {
				rs.close();
			} catch (Exception e) {

			}
			try {
				st.close();
			} catch (Exception e) {

			}
			try {
				conn.close();
			} catch (Exception e) {

			}
		}

		return result;
	}

	public static String getSingleValue(String sql, List conditions) {
		List result = getValue(sql, conditions);
		if (result != null && result.size() >= 1) {
			return (String) result.get(0);
		} else {
			return "";
		}
	}

	private static void setParamValue(PreparedStatement pstmt, int index,
			Object obj) throws Exception {
		if (obj != null) {
			if (obj instanceof String)
				pstmt.setString(index, (String) obj);
			else if (obj instanceof Integer) {
				Integer k = (Integer) obj;
				pstmt.setInt(index, k.intValue());
			} else if (obj instanceof java.util.Date) {
				pstmt.setDate(index, (Date) obj);
			} else if (obj instanceof java.sql.Date) {
				pstmt.setDate(index, (Date) obj);
			} else
				pstmt.setString(index, (String) obj);
		} else
			pstmt.setObject(index, null);
	}

	public static List getRecords(String sql, List conditions) {
		Connection conn = null;
		PreparedStatement st = null;
		ResultSet rs = null;
		List result = new ArrayList();
		try {
			conn = getConnection();
			st = conn.prepareStatement(sql);
			if (conditions != null) {
				for (int i = 0; i < conditions.size(); i++) {
					setParamValue(st, i + 1, conditions.get(i));
				}
			}
			rs = st.executeQuery();
			while (rs.next()) {
				result.add(rs.getString(1));
			}
		} catch (Exception e) {
			throw new SystemException(ErrorCode.DB_ERROR, e);
		} finally {
			try {
				rs.close();
			} catch (Exception e) {

			}
			try {
				st.close();
			} catch (Exception e) {

			}
			try {
				conn.close();
			} catch (Exception e) {

			}
		}

		return result;
	}

}
